from __future__ import print_function
from collections import Counter
import cx_Oracle
import time
import gc

import config

# configuration
# db_user = config.db_user
# db_pwd = config.db_pwd
# dsn = config.dsn

# may need to convert the imsi_deviceid's type according to its type in database type
# this is the case the imsi_deviceid is number in database
# imsi_deviceids = config.imsi_deviceids
# wifi_deviceids = config.wifi_deviceids

imsi_wifi_deviceid_dict = dict(zip(config.imsi_deviceids, config.wifi_deviceids))
wifi_imsi_deviceid_dict = dict(zip(config.wifi_deviceids, config.imsi_deviceids))

connection = cx_Oracle.connect(config.db_user, config.db_pwd, config.dsn)
# maybe commit manually can be more efficient
connection.autocommit = True

# caches
imsi_cache = {}
mac_cache = {}
imsi_freq_cache = {}
mac_freq_cache = {}


def clear_imsi_cache():
    global imsi_cache
    imsi_cache.clear()


def clear_mac_cache():
    global mac_cache
    mac_cache.clear()


def clear_imsi_freq_cache():
    global imsi_freq_cache
    imsi_freq_cache.clear()


def clear_mac_freq_cache():
    global mac_freq_cache
    mac_freq_cache.clear()


def clear_all_caches():
    clear_imsi_cache()
    clear_mac_cache()
    clear_imsi_freq_cache()
    clear_mac_freq_cache()
    gc.collect()


#
#
# def save_imsi_caches():
#     pass
#
#
#
# def save_wifi_caches():
#     pass


# imsi_freq_cache, {imsi: freq}
# persist imsi_freq_cache to the database
def save_imsi_freq_cache():
    sql = ('merge into imsi_freq d '
           'using (select :imsi_v imsi from dual) s '
           'on (d.imsi = s.imsi) '
           'when matched then update set d.freq = :freq_v, d.updatetime = :cur_t_v '
           'when not matched then insert (d.imsi, d.freq, d.updatetime) '
           '  values(:imsi_v, :freq_v, :cur_t_v)')
    # print(sql)
    cursor = connection.cursor()
    cursor.prepare(sql)
    for imsi, freq in imsi_freq_cache.items():
        cur_t = int(time.time())
        cursor.execute(None, {'imsi_v': imsi, 'freq_v': freq, 'cur_t_v': cur_t})
    cursor.close()


# mac_freq_cache: {mac: freq}
# persist mac_freq_cache
def save_mac_freq_cache():
    sql = ('merge into mac_freq d '
           'using (select :mac_v mac from dual) s '
           'on (d.mac = s.mac) '
           'when matched then update set d.freq = :freq_v, d.updatetime = :cur_t_v '
           'when not matched then insert (d.mac, d.freq, d.updatetime) '
           '  values(:mac_v, :freq_v, :cur_t_v)')
    cursor = connection.cursor()
    cursor.prepare(sql)
    for mac, freq in mac_freq_cache.items():
        cur_t = int(time.time())
        cursor.execute(None, {'mac_v': mac, 'freq_v': freq, 'cur_t_v': cur_t})
    cursor.close()


# save or update verify_items to table imsi_mac
# verify_items: [(imsi, mac, collision_times)]
def save_verify_items(verify_items):
    sql = ('merge into imsi_mac d '
           'using (select :imsi_val imsi, :mac_val mac from dual) s '
           'on (d.imsi = s.imsi and d.mac = s.mac) '
           'WHEN MATCHED THEN UPDATE  set d.collisions = :co_val, d.updatetime = :cur_t_val '
           'WHEN NOT MATCHED THEN INSERT  (d.imsi, d.mac, d.collisions, d.updatetime) '
           ' values(:imsi_val, :mac_val, :co_val, :cur_t_val)')
    # print(sql)
    cursor = connection.cursor()
    cursor.prepare(sql)
    for imsi, mac, collision_times in verify_items:
        cur_t = int(time.time())
        # print(imsi, mac, collision_times)
        cursor.execute(None, {'imsi_val': imsi, 'mac_val': mac,
                              'co_val': collision_times, 'cur_t_val': cur_t})
        # connection.commit()
    cursor.close()


def save_vefify_items_with_threshold(verify_items, collisions_threshold):
    sql = ('merge into imsi_mac d '
           'using (select :imsi_val imsi, :mac_val mac from dual) s '
           'on (d.imsi = s.imsi and d.mac = s.mac) '
           'WHEN MATCHED THEN UPDATE  set d.collisions = :co_val, d.updatetime = :cur_t_val '
           'WHEN NOT MATCHED THEN INSERT  (d.imsi, d.mac, d.collisions, d.updatetime) '
           ' values(:imsi_val, :mac_val, :co_val, :cur_t_val)')
    # print(sql)
    cursor = connection.cursor()
    cursor.prepare(sql)
    for imsi, mac, collision_times in verify_items:
        if collision_times >= collisions_threshold:
            cur_t = int(time.time())
            # print(imsi, mac, collision_times)
            cursor.execute(None, {'imsi_val': imsi, 'mac_val': mac,
                                  'co_val': collision_times, 'cur_t_val': cur_t})
            # connection.commit()
    cursor.close()


def save_all_freq_caches():
    save_imsi_freq_cache()
    save_mac_freq_cache()


#
# return: [(deviceid, capturetime)]
def get_imsi_info(imsi):
    r = imsi_cache.get(imsi, None)
    if r is None:
        sql = 'SELECT deviceid, capturetime FROM imsi_data WHERE imsi = :imsi_v'
        cursor = connection.cursor()
        sql_r = cursor.execute(sql, {'imsi_v': imsi})
        r = list(sql_r)
        cursor.close()
        imsi_cache[imsi] = r
    return r


#
# return: [(deviceid, intime, outtime)]
def get_mac_info(mac):
    r = mac_cache.get(mac, None)
    if r is None:
        sql = 'SELECT deviceid, intime, outtime FROM wifi_data WHERE mac = :mac_v'
        cursor = connection.cursor()
        sql_r = cursor.execute(sql, {'mac_v': mac})
        r = list(sql_r)
        cursor.close()
        mac_cache[mac] = r
    return r


#
# return (freq, updatetime), if there is no record in the database, return (-1, -1)
def get_imsi_freq(imsi):
    r = imsi_freq_cache.get(imsi, None)
    if r is None:
        sql = 'SELECT freq, updatetime FROM imsi_freq WHERE imsi = :imsi_v'
        cursor = connection.cursor()
        sql_r = cursor.execute(sql, {'imsi_v': imsi})
        list_r = list(sql_r)
        # print(list_r)
        cursor.close()
        if len(list_r) == 0:
            r = (-1, -1)
        else:
            r = list_r[0]
        imsi_freq_cache[imsi] = r
    return r


#
# return (freq, updatetime), if there is no record in the database, return (-1, -1)
def get_mac_freq(mac):
    r = mac_freq_cache.get(mac, None)
    if r is None:
        sql = 'SELECT freq, updatetime FROM mac_freq WHERE mac = :mac_v'
        cursor = connection.cursor()
        sql_r = cursor.execute(sql, {'mac_v': mac})
        list_r = list(sql_r)
        cursor.close()
        if len(list_r) == 0:
            r = (-1, -1)
        else:
            r = list_r[0]
        imsi_freq_cache[mac] = r
    return r


# this fuction(or algorithm) will find related imsi and mac in time from t to (t + window_size)
# then will verify the pair (imsi, mac) in the database to find the collision times
# and then will persist collison times, every imsi or mac appear times in the database
def process_data_with_window(t, window_size, r_t_delta, v_t_delta, collisions_threshold):
    imsi_mac_pairs = get_related_imsi_mac(t, window_size, r_t_delta)
    verify_items = verify_imsi_mac_pairs(imsi_mac_pairs, v_t_delta)
    save_vefify_items_with_threshold(verify_items, collisions_threshold)
    save_all_freq_caches()
    clear_all_caches()


# todo: need more test
def get_related_imsi_mac(t, window_size, t_delta):
    imsi_list = get_delimit_imsis(t, window_size)
    # print(imsi_list)
    mac_list = get_delimit_macs(t, window_size)
    # print(mac_list)
    imsi_mac_pairs = []
    for imsi_d_id, imsi, capturetime in imsi_list:
        for wifi_d_id, mac, intime, outtime in mac_list:
            if wifi_d_id == imsi_wifi_deviceid_dict[imsi_d_id] and intime - t_delta <= capturetime <= outtime + t_delta:
                imsi_mac_pairs.append((imsi, mac))
    return imsi_mac_pairs


# return: [(deviceid, imsi, capturetime)]
def get_delimit_imsis(t, window_size):
    imsi_sql = "SELECT deviceid, imsi, capturetime FROM imsi_data " + \
               "WHERE capturetime >= :start_t AND capturetime <= :end_t "
    cursor = connection.cursor()
    cursor.prepare(imsi_sql)
    r = cursor.execute(None, {'start_t': t, 'end_t': t + window_size})
    imsi_list = list(r)
    cursor.close()
    return imsi_list


# return: [(deviceid, mac, intime, outtime)]
def get_delimit_macs(t, windows_size):
    wifi_sql = "SELECT deviceid, mac, intime, outtime FROM wifi_data " + \
               "WHERE (intime >= :start_t AND intime <= :end_t) " + \
               "OR (outtime >= :start_t AND outtime <= :end_t) "
    cursor = connection.cursor()
    cursor.prepare(wifi_sql)
    r = cursor.execute(None, {'start_t': t, 'end_t': windows_size + t})
    mac_list = list(r)
    cursor.close()
    return mac_list


# find related imsi and mac, from t to (t + window_size) with deviceid specified
# t : the unix time, the start time to find possible related imsi and mac(wifi)
# besides, t + window_size is the end time, and window_size is the length of time to consider
# if mac.intime - t_delta <= imsi.capturetime <= mac.outtime, we get a pair (imsi, mac)
# this function returns [(imsi, mac)]
# may need more test
def get_related_imsi_mac_with_deviceid(t, window_size, t_delta, imsi_deviceid):
    imsi_list = get_delimit_imsis_with_deviceid(t, window_size, imsi_deviceid)
    # print(len(imsi_list))
    # print(imsi_list)
    wifi_deviceid = imsi_wifi_deviceid_dict[imsi_deviceid]
    mac_list = get_delimit_macs_with_deviceid(t, window_size, wifi_deviceid)
    # print(len(mac_list))
    # print(mac_list)
    imsi_mac_pairs = []
    for imsi, capturetime in imsi_list:
        for mac, intime, outtime in mac_list:
            if intime - t_delta <= capturetime <= outtime + t_delta:
                imsi_mac_pairs.append((imsi, mac))
    return imsi_mac_pairs


# this function will return all the recorded imsi related to imsi_deviceid from time t to (t + window_size)
# the return variable is [(imsi, capturetime)]
def get_delimit_imsis_with_deviceid(t, window_size, imsi_deviceid):
    # imsi_deviceid = int(imsi_deviceid), may need to convert imsi_deviceid type according to
    # its value type in database
    cursor = connection.cursor()

    imsi_sql = "SELECT imsi, capturetime FROM imsi_data " + \
               "WHERE deviceid = :d_id AND capturetime >= :start_t AND capturetime <= :end_t "
    # print(imsi_sql)
    cursor.prepare(imsi_sql)
    r = cursor.execute(None, {'d_id': imsi_deviceid, 'start_t': t, 'end_t': t + window_size})
    imsi_list = list(r)
    cursor.close()
    return imsi_list


# t's format is unix time, this function is similar to get_delimit_imsis
# this function will return all the recorded mac related to wifi_deviceid from time t to (t + window_size)
# this function will return [(mac, intime, outtime)]
def get_delimit_macs_with_deviceid(t, window_size, wifi_deviceid):
    cursor = connection.cursor()
    wifi_sql = "SELECT mac, intime, outtime FROM wifi_data " + \
               "WHERE deviceid = :w_id AND " + \
               "((intime >= :start_t AND intime <= :end_t) " \
               "OR (outtime >= :start_t AND outtime <= :end_t)) "
    # print(wifi_sql)
    cursor.prepare(wifi_sql)
    r = cursor.execute(None, {'w_id': wifi_deviceid, 'start_t': t, 'end_t': t + window_size})
    mac_list = list(r)
    cursor.close()
    return mac_list


# todo: test
# return: [(imsi, mac, freq)]
def verify_imsi_mac_pairs(imsi_mac_pairs, t_delta):
    verify_items = []
    for imsi, mac in set(imsi_mac_pairs):
        freq = verify_imsi_mac_pair(imsi, mac, t_delta)
        verify_items.append((imsi, mac, freq))
    return verify_items


#
# return: freq
def verify_imsi_mac_pair(imsi, mac, t_delta):
    imsi_info = get_imsi_info(imsi)
    mac_info = get_mac_info(mac)
    freq = 0
    for i_d_id, capturetime in imsi_info:
        for m_d_id, intime, outtime in mac_info:
            if m_d_id == imsi_wifi_deviceid_dict[i_d_id] and intime - t_delta <= capturetime <= outtime + t_delta:
                freq += 1
    return freq


# todo: more test
def find_mac_by_imsi(imsi, t_l_limit, t_h_limit, t_delta):
    # imsi_info [(deviceid, capturetime)]
    imsi_info = get_imsi_info(imsi)
    # the following code can be optimized by filter and function closure
    # item[1]: capturetime
    filted_imsi_info = [item for item in imsi_info if (t_l_limit <= item[1] <= t_h_limit)]
    if len(filted_imsi_info) == 0:
        # todo: replace the following to a compatible return statement
        return None
    where_clause = gen_where_clause_with_imsi_info(filted_imsi_info, t_delta)
    sql = 'SELECT mac FROM wifi_data WHERE ' + where_clause
    cursor = connection.cursor()
    r = cursor.execute(sql)
    macs = [item[0] for item in r]
    cursor.close()
    mac_counter = Counter(macs)
    return mac_counter
    # print(len(macs))
    # print(len(set(macs)))
    # print(where_clause)


#
# imsi_info: [(deviceid, capturetime)]
def gen_where_clause_with_imsi_info(imsi_info, t_delta):
    template_str = """ (deviceid='{0}' and {2} >= intime-{1} and {2} <= outtime+{1}) """
    or_conditions = [
        template_str.format(imsi_wifi_deviceid_dict[deviceid], t_delta,
                            capturetime)
        for deviceid, capturetime in imsi_info]
    where_clause = ' or '.join(or_conditions)
    return where_clause


# todo: more test
def filter_counter(counter, freq_threshold):
    result = {}
    for k, v in counter.items():
        if v >= freq_threshold:
            result[k] = v
    return result


# todo: test
def find_imsi_by_mac(mac, t_l_limit, t_h_limit, t_delta):
    mac_info = get_mac_info(mac)
    filted_mac_info = [item for item in mac_info if (item[1] >= t_l_limit and item[2] <= t_h_limit)]
    if len(filted_mac_info) == 0:
        # todo: replace the following to a compatible return statement
        return None
    where_clause = gen_where_clause_with_mac_info(filted_mac_info, t_delta)
    sql = 'select imsi from imsi_data where ' + where_clause
    cursor = connection.cursor()
    r = cursor.execute(sql)
    imsis = [item[0] for item in r]
    cursor.close()
    imsi_counter = Counter(imsis)
    return imsi_counter


# todo: test
def gen_where_clause_with_mac_info(mac_info, t_delta):
    # 0: deviceid, 1: t_delta, 2: intime, 3: outtime
    template_str = """ (deviceid={0} and  capturetime >= {2} - {1} and capturetime <= {3} + {1}) """
    or_conditions = [template_str.format(wifi_imsi_deviceid_dict[deviceid], t_delta, intime, outtime) for
                     deviceid, intime, outtime in mac_info]
    where_clause = ' or '.join(or_conditions)
    return where_clause


# return: [(imsi, mac, freq)]
def find_mac_by_imsi_with_freq_threshold(imsi, t_l_limit=config.f_t_l_limit, t_h_limit=config.f_t_h_limit, t_delta=config.f_t_delta, freq_threshold=config.freq_threshold):
    counter = find_mac_by_imsi(imsi, t_l_limit, t_h_limit, t_delta)
    if counter is None:
        return []
    filted_map = filter_counter(counter, freq_threshold)
    result = [(imsi, mac, freq) for mac, freq in filted_map.items()]
    save_verify_items(result)
    # todo: may need to save (imsi, freq) and (mac, freq)
    return result


# return: [(imsi, mac, freq)]
def find_imsi_by_mac_with_freq_threshold(mac, t_l_limit=config.f_t_l_limit, t_h_limit=config.f_t_h_limit, t_delta=config.f_t_delta, freq_threshold=config.freq_threshold):
    counter = find_imsi_by_mac(mac, t_l_limit, t_h_limit, t_delta)
    if counter is None:
        return []
    filted_map = filter_counter(counter, freq_threshold)
    result = [(imsi, mac, freq) for imsi, freq in filted_map.items()]
    save_verify_items(result)
    # todo: may need to save (imsi, freq) and (mac, freq)
    return result


# return [(imsi, mac, freq)]
def select_by_imsi(part_imsi, rowlimit=config.select_result_limit):
    sql = """select imsi, mac, collisions from imsi_mac where imsi like '%{0}%' and rownum <= :rlimit"""
    cursor = connection.cursor()
    r = cursor.execute(sql.format(part_imsi), {'rlimit': rowlimit})
    result = list(r)
    cursor.close()
    return result


# return: [(imsi, mac, freq)]
def select_by_mac(part_mac, rowlimit=config.select_result_limit):
    sql = """select imsi, mac, collisions from imsi_mac where mac like '%{0}%' and rownum <= :rlimit"""
    cursor = connection.cursor()
    r = cursor.execute(sql.format(part_mac), {'rlimit': rowlimit})
    result = list(r)
    cursor.close()
    return result
